package com.katian;

import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.*;

/**
 * User: csavard
 * Date: 13-06-16
 */
public final class ExcelUtil {
    /**
     * Contructeur prive pour ne pas etre instatié
     */
    private ExcelUtil() {
    }


    public static void autoResizeAllColumn(XSSFSheet sheet) {
        int columIndex = 0;
        int lastColumn = 0;
        XSSFRow firstRow = sheet.getRow(0);
        XSSFRow lastRow = sheet.getRow(sheet.getLastRowNum());
        if (firstRow != null) {
            lastColumn = firstRow.getLastCellNum();
        }
        if (lastRow != null && lastRow.getLastCellNum() > lastColumn) {
            lastColumn = lastRow.getLastCellNum();
        }

        while (columIndex <= lastColumn) {
            sheet.autoSizeColumn(columIndex);
            columIndex++;
        }
    }

    public static void setRowBackground(Row row, IndexedColors color) {
        int colIndex = 0;
        while (colIndex < row.getLastCellNum()) {
            Cell cell = row.getCell(colIndex);
            if (cell == null) {
                cell = row.createCell(colIndex);
            }

            Workbook workbook = row.getSheet().getWorkbook();
            CellStyle newStyle = workbook.createCellStyle();
            newStyle.cloneStyleFrom(cell.getCellStyle());
            newStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
            newStyle.setFillForegroundColor(color.getIndex());

            cell.setCellStyle(newStyle);

            colIndex++;
        }
    }

    public static void insertColumn(XSSFRow row, int index) {
        short lastColumn = row.getLastCellNum();
        for (int i = lastColumn; i >= index; i--) {
            XSSFCell cellOld = row.getCell(i);
            XSSFCell cellNew = row.getCell(i + 1);
            if (cellOld != null && cellNew != null) {
                copyCell(cellOld, cellNew);
            }

        }
        row.getCell(index).setCellValue("");
    }

    public static void copyCell(XSSFCell cellOld, XSSFCell cellNew) {
        copyCell(cellOld, cellNew, true);
    }

    public static void copyCell(XSSFCell oldCell, XSSFCell newCell, boolean copyFormula) {

        switch (oldCell.getCellType()) {
            case XSSFCell.CELL_TYPE_STRING:
                newCell.setCellValue(oldCell.getStringCellValue());
                break;
            case XSSFCell.CELL_TYPE_NUMERIC:
                newCell.setCellValue(oldCell.getNumericCellValue());
                break;
            case XSSFCell.CELL_TYPE_BLANK:
                newCell.setCellValue("");
                break;
            case XSSFCell.CELL_TYPE_BOOLEAN:
                newCell.setCellValue(oldCell.getBooleanCellValue());
                break;
            case XSSFCell.CELL_TYPE_FORMULA:
                if (copyFormula) {
                    newCell.setCellFormula(oldCell.getCellFormula());
                } else {
                    newCell.setCellValue(oldCell.getStringCellValue());
                }
                break;
        }
    }

    public static String getStringValue(XSSFCell cell) {
        String value = null;
        if (cell != null) {
            switch (cell.getCellType()) {
                case XSSFCell.CELL_TYPE_STRING:
                    value = cell.getStringCellValue();
                    break;
                case XSSFCell.CELL_TYPE_NUMERIC:
                    value = String.valueOf(cell.getNumericCellValue());
                    break;
                case XSSFCell.CELL_TYPE_BLANK:
                    break;
                case XSSFCell.CELL_TYPE_BOOLEAN:
                    value = String.valueOf(cell.getBooleanCellValue());
                    break;
                case XSSFCell.CELL_TYPE_FORMULA:
                    XSSFFormulaEvaluator formulaEvaluator = cell.getRow().getSheet().getWorkbook().getCreationHelper().createFormulaEvaluator();
                    CellValue evaluate = formulaEvaluator.evaluate(cell);
                    value = evaluate.formatAsString();
                    break;
            }
        }

        return value;
    }



    public static void setCellComment(Cell cell, String text) {
        Row row = cell.getRow();
        Sheet sheet = row.getSheet();
        CreationHelper factory = sheet.getWorkbook().getCreationHelper();

        Drawing drawing = sheet.createDrawingPatriarch();

        // When the comment box is visible, have it show in a 1x3 space
        ClientAnchor anchor = factory.createClientAnchor();
        anchor.setCol1(cell.getColumnIndex());
        anchor.setCol2(cell.getColumnIndex() + 3);
        anchor.setRow1(row.getRowNum());
        anchor.setRow2(row.getRowNum() + 3);

        // Create the comment and set the text+author
        Comment comment = drawing.createCellComment(anchor);
        RichTextString str = factory.createRichTextString(text);
        comment.setString(str);
        comment.setAuthor("Katian");

        // Assign the comment to the cell
        cell.setCellComment(comment);
    }

    public static void copySheet(XSSFSheet source, XSSFSheet destination) {
        int rowCount = source.getLastRowNum();
        int rowIndex = 0;
        while (rowIndex <= rowCount) {
            XSSFRow sourceRow = source.getRow(rowIndex);
            if (sourceRow != null) {
                XSSFRow destinationRow = destination.createRow(rowIndex);
                short lastCellNum = sourceRow.getLastCellNum();
                int columnIndex = 0;
                while (columnIndex <= lastCellNum) {
                    XSSFCell sourceCell = sourceRow.getCell(columnIndex);
                    if (sourceCell != null) {
                        XSSFCell destinationCell = destinationRow.createCell(columnIndex);
                        copyCell(sourceCell, destinationCell, false);
                    }
                    columnIndex++;
                }
            }

            rowIndex++;
        }
    }


    public static void removeCell(Row row, int no) {
        removeCell(row, no, no);
    }

    public static void removeCell(Row row, int firstCol, int lastCol) {
        for (int i = firstCol; i <= lastCol; i++) {
            Cell cell = row.getCell(i);
            if (cell != null) {
                row.removeCell(cell);
            }
        }
    }

}
